﻿<DataListSpec 
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes" 
	ID="b86ab5a7-d83d-4e65-860f-43a7135abe67" 
	Name="AddressesChangedSince Data List"
	Description="Get Addresses Changed or Inserted After a Specific Date/Time" 
	Author="Blackbaud Professional Services"
	common:SecurityUIFolder="Address">

	<!-- define the SP that returns the data for the datalist -->
	<SPDataList SPName="USP_DATALIST_ADDRESSESCHANGEDSINCE">
		<common:CreateProcedureSQL>
			<![CDATA[
create procedure dbo.USP_DATALIST_ADDRESSESCHANGEDSINCE(
  @UPDATEDSINCE datetime)
as
	set nocount on;
  
SELECT
  a.ID,
	a.CONSTITUENTID,
	at.DESCRIPTION  as ADDRESSTYPE,
	s.DESCRIPTION   as STATEFULLNAME,
	s.ABBREVIATION  as STATEABBREVIATEDNAME,
	c.DESCRIPTION   as COUNTRYFULLNAME, 
	c.ABBREVIATION  as COUNTRYABBREVIATEDNAME, 
	a.ISPRIMARY, 
	a.STARTDATE, 
	a.ENDDATE, 
	a.ADDRESSBLOCK, 
	a.CITY, 
	a.POSTCODE, 
	a.DESCRIPTION, 
	a.ISCONFIDENTIAL,
  ca.USERNAME as CHANGEDBYUSERNAME
  
FROM ADDRESS a
	LEFT OUTER JOIN COUNTRY         c   ON a.COUNTRYID          = c.ID
	LEFT OUTER JOIN STATE           s   ON a.STATEID            = s.ID
	LEFT OUTER JOIN ADDRESSTYPECODE at  ON a.ADDRESSTYPECODEID  = at.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID = a.CHANGEDBYID



WHERE a.DATECHANGED >= @UPDATEDSINCE
AND CA.USERNAME <> 'NT AUTHORITY\NETWORK SERVICE'
  
  
			]]>
		</common:CreateProcedureSQL>
	</SPDataList>


	<!-- describe any parameters (other than the context ID) defined on the SP -->
	<Parameters>
		<common:FormMetaData>
			<common:FormFields>
				<common:FormField FieldID="UPDATEDSINCE" Caption="Updated Since" DataType="Date"/>
			</common:FormFields>
		</common:FormMetaData>
	</Parameters>

	<!-- define the output fields in the list -->
	<Output>
		<OutputFields>
			<!-- 0 -->
      <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
      <!-- 1 -->
      <OutputField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid" />
      <!-- 2 -->
      <OutputField FieldID="ADDRESSTYPE" Caption="Address Type" DataType="String" />
      <!-- 3 -->
      <OutputField FieldID="STATEFULLNAME" Caption="State Full Name" DataType="String" />
      <!-- 4 -->
      <OutputField FieldID="STATEABBREVIATEDNAME" Caption="State Abbreviated Name" DataType="String" />
      <!-- 5 -->
      <OutputField FieldID="COUNTRYFULLNAME" Caption="Country Full Name" DataType="String" />
      <!-- 6 -->
      <OutputField FieldID="COUNTRYABBREVIATEDNAME" Caption="Country Abbreviated Name" DataType="String" />
      <!-- 7 -->
      <OutputField FieldID="ISPRIMARY" Caption="Is Primary" DataType="Boolean" />
      <!-- 8 -->
      <OutputField FieldID="STARTDATE" Caption="Start Date" DataType="String" />
      <!-- 9 -->
      <OutputField FieldID="ENDDATE" Caption="End Date" DataType="String" />
      <!-- 10 -->
      <OutputField FieldID="ADDRESSBLOCK" Caption="Address Block" DataType="String" />
      <!-- 11 -->
      <OutputField FieldID="CITY" Caption="Ciy" DataType="String" />
      <!-- 12 -->
      <OutputField FieldID="POSTCODE" Caption="Post Code" DataType="String" />
      <!-- 13 -->
      <OutputField FieldID="DESCRIPTION" Caption="Description" DataType="String" />
      <!-- 14 -->
      <OutputField FieldID="ISCONFIDENTIAL" Caption="Is Confidential" DataType="Boolean" />
      <!-- 15 -->
      <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
    </OutputFields>
	</Output>
</DataListSpec>
